﻿/*
This library is free software; you can redistribute it and/or
modify it under the terms of the GNU Lesser General Public
License as published by the Free Software Foundation; either
version 2.1 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public
License along with this library; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Diagnostics;
using System.Reflection;
using System.Globalization;
using System.Data;

namespace IvyOrm
{
    /// <summary>
    /// Provides RecordInsert, RecordUpdate and RecordDelete extensions to the DbConnection and DbTransaction object.
    /// </summary>
    public static class DataModificationExtension
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1308:NormalizeStringsToUppercase")]
        private static Func<string, string> getFormatIdentifierFunc(ModificationOptions? options)
        {
            Debug.Assert(options != null);

            var options1 = (ModificationOptions)options;
            Func<string, string> formatIdentifier;
            if ((options1 & ModificationOptions.UppercaseIdentifiers) == ModificationOptions.UppercaseIdentifiers)
                formatIdentifier = (s) => s.ToUpperInvariant();
            else if ((options1 & ModificationOptions.LowercaseIdentifiers) == ModificationOptions.LowercaseIdentifiers)
                formatIdentifier = (s) => s.ToLowerInvariant();
            else
                formatIdentifier = (s) => s;
            return formatIdentifier;
        }


        #region RecordInsert DbConnection
        /// <summary>
        /// Executes a SQL insert for the provided record.
        /// 
        /// Additional options can be specified using TableAttribute, ColumnAttribute and 
        /// PrimaryKeyAttribute on the record type.
        /// </summary>
        /// <param name="connection">Connection to execute the command on.</param>
        /// <param name="record">
        /// The object that will be used to generate the SQL command and parameters.
        /// </param>
        public static void RecordInsert(this DbConnection connection, object record)
        {
            RecordInsert(connection, new IvyOrmOptions(), record);
        }

        /// <summary>
        /// Executes a SQL insert for the provided record.
        /// 
        /// Additional options can be specified using TableAttribute, ColumnAttribute and 
        /// PrimaryKeyAttribute on the record type.
        /// </summary>
        /// <param name="connection">Connection to execute the command on.</param>
        /// <param name="options">
        /// Specifies additional options to be used with the operation.
        /// </param>
        /// <param name="record">
        /// The object that will be used to generate the SQL command and parameters.
        /// </param>
        public static void RecordInsert(this DbConnection connection, IvyOrmOptions options, object record)
        {
            if (connection == null)
                throw new ArgumentNullException("connection");
            if (record == null)
                throw new ArgumentNullException("record");

            using(var command = connection.CreateCommand())
            {
                recordInsertExecute(command, options, record);
            }
        }
        #endregion

        #region RecordInsert DbTransaction
        /// <summary>
        /// Executes a SQL insert for the provided record.
        /// 
        /// Additional options can be specified using TableAttribute, ColumnAttribute and 
        /// PrimaryKeyAttribute on the record type.
        /// </summary>
        /// <param name="transaction">Transaction to execute the command on.</param>
        /// <param name="record">
        /// The object that will be used to generate the SQL command and parameters.
        /// </param>
        public static void RecordInsert(this DbTransaction transaction, object record)
        {
            RecordInsert(transaction, new IvyOrmOptions(), record);
        }

        /// <summary>
        /// Executes a SQL insert for the provided record.
        /// 
        /// Additional options can be specified using TableAttribute, ColumnAttribute and 
        /// PrimaryKeyAttribute on the record type.
        /// </summary>
        /// <param name="transaction">Transaction to execute the command on.</param>
        /// <param name="options">
        /// Specifies additional options to be used with the operation.
        /// </param>
        /// <param name="record">
        /// The object that will be used to generate the SQL command and parameters.
        /// </param>
        public static void RecordInsert(this DbTransaction transaction, IvyOrmOptions options, object record)
        {
            if (transaction == null)
                throw new ArgumentNullException("transaction");
            if (transaction.Connection == null)
                throw new ArgumentException("must have the connection property set", "transaction");
            if (record == null)
                throw new ArgumentNullException("record");


            using (var command = transaction.Connection.CreateCommand())
            {
                command.Transaction = transaction;

                recordInsertExecute(command, options, record);
            }
        }
        #endregion
        
        static Dictionary<Type, DbType> typeToDbType;
        private static DbType getDbType(Type type)
        {
            if (typeToDbType == null)
            {
                var typeMap = new Dictionary<Type, DbType>();
                typeMap.Add(typeof(byte), DbType.Byte);
                typeMap.Add(typeof(sbyte), DbType.SByte);
                typeMap.Add(typeof(short), DbType.Int16);
                typeMap.Add(typeof(ushort), DbType.UInt16);
                typeMap.Add(typeof(int), DbType.Int32);
                typeMap.Add(typeof(uint), DbType.UInt32);
                typeMap.Add(typeof(long), DbType.Int64);
                typeMap.Add(typeof(ulong), DbType.UInt64);
                typeMap.Add(typeof(float), DbType.Single);
                typeMap.Add(typeof(double), DbType.Double);
                typeMap.Add(typeof(decimal), DbType.Decimal);
                typeMap.Add(typeof(bool), DbType.Boolean);
                typeMap.Add(typeof(string), DbType.String);
                typeMap.Add(typeof(char), DbType.StringFixedLength);
                typeMap.Add(typeof(Guid), DbType.Guid);
                typeMap.Add(typeof(DateTime), DbType.DateTime);
                typeMap.Add(typeof(DateTimeOffset), DbType.DateTimeOffset);
                typeMap.Add(typeof(byte[]), DbType.Binary);
                typeMap.Add(typeof(byte?), DbType.Byte);
                typeMap.Add(typeof(sbyte?), DbType.SByte);
                typeMap.Add(typeof(short?), DbType.Int16);
                typeMap.Add(typeof(ushort?), DbType.UInt16);
                typeMap.Add(typeof(int?), DbType.Int32);
                typeMap.Add(typeof(uint?), DbType.UInt32);
                typeMap.Add(typeof(long?), DbType.Int64);
                typeMap.Add(typeof(ulong?), DbType.UInt64);
                typeMap.Add(typeof(float?), DbType.Single);
                typeMap.Add(typeof(double?), DbType.Double);
                typeMap.Add(typeof(decimal?), DbType.Decimal);
                typeMap.Add(typeof(bool?), DbType.Boolean);
                typeMap.Add(typeof(char?), DbType.StringFixedLength);
                typeMap.Add(typeof(Guid?), DbType.Guid);
                typeMap.Add(typeof(DateTime?), DbType.DateTime);
                typeMap.Add(typeof(DateTimeOffset?), DbType.DateTimeOffset);
                typeToDbType = typeMap;
            }

            return typeToDbType[type];
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Maintainability", "CA1505:AvoidUnmaintainableCode"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Maintainability", "CA1502:AvoidExcessiveComplexity"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Maintainability", "CA1506:AvoidExcessiveClassCoupling"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        internal static void recordInsertExecute(DbCommand command, IvyOrmOptions options , object record)
        {
            Debug.Assert(command != null);
            Debug.Assert(command.Connection != null);
            Debug.Assert(record != null);

            options = IvyOrmOptions.Defaults(command.Connection, options);
            Debug.Assert(options != null);

            var factory = options.DbProviderFactory;
            var defaultConverterFactory = options.ValueConverterFactory;
            var modificationOptions = options.ModificationOptions;
            Debug.Assert(defaultConverterFactory != null);
            Debug.Assert(factory != null);

            var classInfo = IvyClassInfo.GetClassInfo(record.GetType());
            Debug.Assert(classInfo != null);

            Func<string, string> formatIdentifier = getFormatIdentifierFunc(modificationOptions);
            Debug.Assert(formatIdentifier != null);

            int paramNo = 0;

            using (var commandBuilder = factory.CreateCommandBuilder())
            {
                #region Prepair Command
                {
                    StringBuilder columns = new StringBuilder();
                    StringBuilder values = new StringBuilder();
                    List<DbParameter> parameters = new List<DbParameter>();
                    Debug.Assert(classInfo.Columns.Count > 0);
                    foreach (var keyPair in classInfo.Columns.Where((c) => c.Value.IncludeInInsert))
                    {
                        var columnName = keyPair.Key;
                        var parameterName = commandBuilder.GetParameterPlaceholder(paramNo++);
                        var columnInfo = keyPair.Value;

                        //Adds to string builder
                        if (columns.Length != 0)
                            columns.Append(",");
                        if (values.Length != 0)
                            values.Append(",");
                        columns.Append(commandBuilder.QuoteIdentifier(formatIdentifier(columnName)));
                        values.Append(parameterName);


                        //Retrieves member value
                        object preconvertedValue = columnInfo.GetValueUnconverted(record);
                        var converter = (columnInfo.ConverterFactory ?? defaultConverterFactory).GetToDatabaseConverter(columnInfo.FieldOrPropertyType, columnInfo.DatabaseType);
                        var postConvertedValue = converter(preconvertedValue);

                        //Adds parameter
                        var parameter = factory.CreateParameter();
                        parameter.ParameterName = parameterName;
                        parameter.Value = postConvertedValue == null ? DBNull.Value : postConvertedValue;
                        parameters.Add(parameter);
                        command.Parameters.Add(parameter);
                    }

                    command.CommandText = string.Format(CultureInfo.InvariantCulture, "insert into {0} ({1}) values ({2})", commandBuilder.QuoteIdentifier(formatIdentifier(classInfo.TableName)), columns, values);

                    Logging.Log(options, TraceEventType.Information, 1879036, string.Format(CultureInfo.InvariantCulture, "RecordInsert with the following command: \n{0}", command.CommandText));
                }
                #endregion

                #region Execute Command
                var returnPrimaryKeys = classInfo.Columns.Values.Where((c) => c.PrimaryKeyReturn).ToList();
                if (returnPrimaryKeys.Count == 0)
                {
                    //no return key
                    command.ExecuteNonQuery();
                }
                else
                {
                    Action<IvyColumnInfo, object> SetValue = (column, value) =>
                    {
                        if (value == DBNull.Value)
                            value = null;
                        var sourceType = value == null ? typeof(object) : value.GetType();
                        var converter = (column.ConverterFactory ?? defaultConverterFactory).GetFromDatabaseConverter(sourceType, column.FieldOrPropertyType);
                        var convertedValue = converter(value);

                        column.SetValueConverted(record, convertedValue);
                    };


                    //
                    // Provider Return Primary Key Values
                    //
                    if (command.Connection.GetType().Name == KnownConnectionTypes.SQLiteConnection)
                    {
                        if (returnPrimaryKeys.Count > 1)
                        {
                            var message = "Returning multiple primary keys is not supported for " + command.Connection.GetType().Name + ". (9C609BC5)";
                            var exception = new NotSupportedException(message);
                            Logging.Log(options, TraceEventType.Error, 1879037, message, exception);
                            throw exception;
                        }

                        command.CommandText += "; " + Environment.NewLine + "select last_insert_rowid()";
                        var returnedValue = command.ExecuteScalar();
                        SetValue(returnPrimaryKeys[0], returnedValue);
                    }
                    else if (command.Connection.GetType().Name == KnownConnectionTypes.MySqlConnection)
                    {
                        if (returnPrimaryKeys.Count > 1)
                        {
                            var message = "Returning multiple primary keys is not supported for " + command.Connection.GetType().Name + ". (9C609BC5)";
                            var exception = new NotSupportedException(message);
                            Logging.Log(options, TraceEventType.Error, 1879037, message, exception);
                            throw exception;
                        }

                        command.CommandText += "; " + Environment.NewLine + "select last_insert_id()";
                        var returnedValue = command.ExecuteScalar();
                        SetValue(returnPrimaryKeys[0], returnedValue);
                    }
                    else if (command.Connection.GetType().Name == KnownConnectionTypes.FbConnection) //FireBird
                    {
                        if (returnPrimaryKeys.Count > 1)
                        {
                            var message = "Returning multiple primary keys is not supported for " + command.Connection.GetType().Name + ". (9C609BC5)";
                            var exception = new NotSupportedException(message);
                            Logging.Log(options, TraceEventType.Error, 1879037, message, exception);
                            throw exception;
                        }

                        var returnPrimaryKey = returnPrimaryKeys[0];

                        command.CommandText += " returning " + commandBuilder.QuoteIdentifier(formatIdentifier(returnPrimaryKey.ColumnName));
                        var returnedValue = command.ExecuteScalar();
                        SetValue(returnPrimaryKeys[0], returnedValue);
                    }
                    else if (command.Connection.GetType().Name == KnownConnectionTypes.SqlConnection)
                    {
                        if (returnPrimaryKeys.Count > 1)
                        {
                            var message = "Returning multiple primary keys is not supported for " + command.Connection.GetType().Name + ". (9C609BC5)";
                            var exception = new NotSupportedException(message);
                            Logging.Log(options, TraceEventType.Error, 1879037, message, exception);
                            throw exception;
                        }

                        command.CommandText += "; " + Environment.NewLine + "select scope_identity()";
                        var returnedValue = command.ExecuteScalar();
                        SetValue(returnPrimaryKeys[0], returnedValue);
                    }
                    else if (command.Connection.GetType().Name == KnownConnectionTypes.SqlCeConnection)
                    {
                        if (returnPrimaryKeys.Count > 1)
                        {
                            var message = "Returning multiple primary keys is not supported for " + command.Connection.GetType().Name + ". (9C609BC5)";
                            var exception = new NotSupportedException(message);
                            Logging.Log(options, TraceEventType.Error, 1879037, message, exception);
                            throw exception;
                        }

                        command.ExecuteNonQuery();

                        command.Parameters.Clear();
                        command.CommandText = "select @@identity";
                        var returnedValue = command.ExecuteScalar();
                        SetValue(returnPrimaryKeys[0], returnedValue);
                    }
                    else if (command.Connection.GetType().Name == KnownConnectionTypes.NpgsqlConnection ||
                             command.Connection.GetType().Name == KnownConnectionTypes.PgSqlConnection)
                    {
                        StringBuilder returnList = new StringBuilder();
                        foreach (var returnPrimaryKey in returnPrimaryKeys)
                        {
                            if (returnList.Length != 0)
                                returnList.Append(",");

                            returnList.Append(commandBuilder.QuoteIdentifier(formatIdentifier(returnPrimaryKey.ColumnName)));
                        }
                        command.CommandText += " returning " + returnList.ToString();
                        using (var reader = command.ExecuteReader())
                        {
                            reader.Read();
                            for (int i = 0; i < returnPrimaryKeys.Count(); i++)
                            {
                                var returnPrimaryKey = returnPrimaryKeys[i];
                                var returnedValue = reader[i];
                                SetValue(returnPrimaryKey, returnedValue);
                            }
                        }
                    }
                    else if (command.Connection.GetType().Name == KnownConnectionTypes.OracleConnection)
                    {
                        StringBuilder returnColumns = new StringBuilder();
                        StringBuilder returnParameters = new StringBuilder();
                        List<DbParameter> parameters = new List<DbParameter>();
                        foreach (var returnPrimaryKey in returnPrimaryKeys)
                        {
                            if (returnColumns.Length != 0)
                                returnColumns.Append(",");
                            if (returnParameters.Length != 0)
                                returnParameters.Append(",");

                            returnColumns.Append(commandBuilder.QuoteIdentifier(formatIdentifier(returnPrimaryKey.ColumnName)));

                            var parameterName = commandBuilder.GetParameterPlaceholder(paramNo++);
                            var parameter = command.CreateParameter();
                            parameter.Direction = System.Data.ParameterDirection.ReturnValue;
                            parameter.DbType = getDbType(returnPrimaryKey.DatabaseType);
                            parameter.ParameterName = parameterName;
                            command.Parameters.Add(parameter);
                            if (parameter.DbType == DbType.String)
                                parameter.Size = 4000; //the max size in oracle
                            parameters.Add(parameter);

                            returnParameters.Append(parameterName);
                        }
                        command.CommandText = "begin " + command.CommandText +
                                              " returning " + returnColumns.ToString() + " into " + returnParameters.ToString() + "; end;";

                        command.ExecuteNonQuery();


                        for (int i = 0; i < returnPrimaryKeys.Count(); i++)
                        {
                            var returnPrimaryKey = returnPrimaryKeys[i];
                            var returnedValue = parameters[i].Value;
                            SetValue(returnPrimaryKey, returnedValue);
                        }
                    }
                    else
                    {
                        var message = "Returning primary key is not supported for " + command.Connection.GetType().Name + ". (82EECF69)";
                        var exception = new NotSupportedException(message);
                        Logging.Log(options, TraceEventType.Error, 1879038, message, exception);
                        throw exception;
                    }
                }
                #endregion
            }
        }


        #region RecordUpdate DbConnection
        /// <summary>
        /// Executes a SQL update for the provided record. The record must be have one or more 
        /// fields with PrimaryKeyAttribute set. Will only update fields without 
        /// PrimaryKeyAttribute. If no records are updated then an exception will be raised.
        /// 
        /// Additional options can be specified using TableAttribute, ColumnAttribute and 
        /// PrimaryKeyAttribute on the record type.
        /// </summary>
        /// <param name="connection">Connection to execute the command on.</param>
        /// <param name="record">
        /// The object that will be used to generate the SQL command and parameters.
        /// </param>
        public static void RecordUpdate(this DbConnection connection, object record)
        {
            RecordUpdate(connection, new IvyOrmOptions(), record);
        }

        /// <summary>
        /// Executes a SQL update for the provided record. The record must be have one or more 
        /// fields with PrimaryKeyAttribute set. Will only update fields without 
        /// PrimaryKeyAttribute. If no records are updated then an exception will be raised.
        /// 
        /// Additional options can be specified using TableAttribute, ColumnAttribute and 
        /// PrimaryKeyAttribute on the record type.
        /// </summary>
        /// <param name="connection">Connection to execute the command on.</param>
        /// <param name="options">
        /// Specifies additional options to be used with the operation.
        /// </param>
        /// <param name="record">
        /// The object that will be used to generate the SQL command and parameters.
        /// </param>
        public static void RecordUpdate(this DbConnection connection, IvyOrmOptions options, object record)
        {
            if (connection == null)
                throw new ArgumentNullException("connection");
            if (record == null)
                throw new ArgumentNullException("record");

            using (var command = connection.CreateCommand())
                recordUpdateExecute(command, options, record);
        }
        #endregion

        #region RecordUpdate DbTransaction
        /// <summary>
        /// Executes a SQL update for the provided record. The record must be have one or more 
        /// fields with PrimaryKeyAttribute set. Will only update fields without 
        /// PrimaryKeyAttribute. If no records are updated then an exception will be raised.
        /// 
        /// Additional options can be specified using TableAttribute, ColumnAttribute and 
        /// PrimaryKeyAttribute on the record type.
        /// </summary>
        /// <param name="transaction">Transaction to execute the command on.</param>
        /// <param name="record">
        /// The object that will be used to generate the SQL command and parameters.
        /// </param>
        public static void RecordUpdate(this DbTransaction transaction, object record)
        {
            RecordUpdate(transaction, new IvyOrmOptions(), record);
        }

        /// <summary>
        /// Executes a SQL update for the provided record. The record must be have one or more 
        /// fields with PrimaryKeyAttribute set. Will only update fields without 
        /// PrimaryKeyAttribute. If no records are updated then an exception will be raised.
        /// 
        /// Additional options can be specified using TableAttribute, ColumnAttribute and 
        /// PrimaryKeyAttribute on the record type.
        /// </summary>
        /// <param name="transaction">Transaction to execute the command on.</param>
        /// <param name="options">
        /// Specifies additional options to be used with the operation.
        /// </param>
        /// <param name="record">
        /// The object that will be used to generate the SQL command and parameters.
        /// </param>
        public static void RecordUpdate(this DbTransaction transaction, IvyOrmOptions options, object record)
        {
            if (transaction == null)
                throw new ArgumentNullException("transaction");
            if (record == null)
                throw new ArgumentNullException("record");
            if (transaction.Connection == null)
                throw new ArgumentException("must have the connection property set", "transaction");

            using (var command = transaction.Connection.CreateCommand())
            {
                command.Transaction = transaction;
                recordUpdateExecute(command, options, record);
            }
        }
        #endregion

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Maintainability", "CA1506:AvoidExcessiveClassCoupling"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Maintainability", "CA1502:AvoidExcessiveComplexity"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        private static void recordUpdateExecute(DbCommand command, IvyOrmOptions options, object record)
        {
            Debug.Assert(command != null);
            Debug.Assert(command.Connection != null);
            Debug.Assert(record != null);

            options = IvyOrmOptions.Defaults(command.Connection, options);
            Debug.Assert(options != null);

            var modificationOptions = (ModificationOptions)options.ModificationOptions;
            var defaultConverterFactory = options.ValueConverterFactory;
            Debug.Assert(defaultConverterFactory != null);

            var factory = options.DbProviderFactory;
            Debug.Assert(factory != null);

            var getFormatedIdentifier = getFormatIdentifierFunc(modificationOptions);
            Debug.Assert(getFormatedIdentifier != null);

            var classInfo = IvyClassInfo.GetClassInfo(record.GetType());
            Debug.Assert(classInfo != null);

            if (!classInfo.Columns.Values.Any((c) => c.PrimaryKey))
            {
                var message = "Unable to perform operation on class with no primary key. Class " + record.GetType().FullName + " needs one or more properties with " + typeof(PrimaryKeyAttribute).Name;
                var exception = new NotSupportedException(message);
                Logging.Log(options, TraceEventType.Error, 1879022, message, exception);
                throw exception;
            }
            if (!classInfo.Columns.Values.Any((c) => !c.PrimaryKey))
            {
                var message = "Unable to perform operation on class with no values to update. Class " + record.GetType().FullName + " needs one or more public properties without " + typeof(PrimaryKeyAttribute).Name;
                var exception = new NotSupportedException(message);
                Logging.Log(options, TraceEventType.Error, 1879023, message, exception);
                throw exception;
            }

            using (var commandBuilder = factory.CreateCommandBuilder())
            {
                int parameterNo = 0;

                //UPDATE piece
                StringBuilder updates = new StringBuilder();
                foreach (var column in classInfo.Columns.Values.Where((c) => !c.PrimaryKey))
                {
                    var columnNameQuoted = commandBuilder.QuoteIdentifier(getFormatedIdentifier(column.ColumnName));
                    var parameterName = commandBuilder.GetParameterPlaceholder(parameterNo++);
                    var converter = (column.ConverterFactory ?? defaultConverterFactory).GetToDatabaseConverter(column.FieldOrPropertyType, column.DatabaseType);
                    var parameterValue = converter(column.GetValueUnconverted(record));

                    var parameter = command.CreateParameter();
                    parameter.ParameterName = parameterName;
                    parameter.Value = parameterValue == null ? DBNull.Value : parameterValue;
                    command.Parameters.Add(parameter);

                    if (updates.Length != 0)
                        updates.Append(",");
                    updates.AppendFormat(CultureInfo.InvariantCulture, "{0}={1}", columnNameQuoted, parameterName);
                }

                StringBuilder where = generateWhereClause(command, defaultConverterFactory, record, getFormatedIdentifier, classInfo, commandBuilder, ref parameterNo);

                command.CommandText = string.Format(CultureInfo.InvariantCulture, "update {0} set {1} where {2}", commandBuilder.QuoteIdentifier(getFormatedIdentifier(classInfo.TableName)), updates, where);

                Logging.Log(options, TraceEventType.Information, 1879026, "RecordUpdate with the following command: \n" + command.CommandText);

                int updatedRecords = command.ExecuteNonQuery();
                if (updatedRecords == 0 && !((modificationOptions & ModificationOptions.DoNotThrowExceptionOnNoRecordsUpdated) == ModificationOptions.DoNotThrowExceptionOnNoRecordsUpdated))
                {
                    var message = string.Format(CultureInfo.InvariantCulture, "Expected to update 1 or more record however {0} records were updated (C006CBFD)", updatedRecords);
                    var exception = new IvyOrmException(message);
                    Logging.Log(options, TraceEventType.Error, 1879025, message, exception);
                    throw exception;
                }
            }
        }


        #region RecordDelete DbConnection
        /// <summary>
        /// Executes a SQL delete for the provided record. The record must be have one or more 
        /// fields with PrimaryKeyAttribute set. If no records are updated then an exception will
        /// be raised.
        /// 
        /// Additional options can be specified using TableAttribute, ColumnAttribute and 
        /// PrimaryKeyAttribute on the record type.
        /// </summary>
        /// <param name="connection">Connection to execute the command on.</param>
        /// <param name="record">
        /// The object that will be used to generate the SQL command and parameters.
        /// </param>
        public static void RecordDelete(this DbConnection connection, object record)
        {
            RecordDelete(connection, new IvyOrmOptions(), record);
        }

        /// <summary>
        /// Executes a SQL delete for the provided record. The record must be have one or more 
        /// fields with PrimaryKeyAttribute set. If no records are updated then an exception will
        /// be raised.
        /// 
        /// Additional options can be specified using TableAttribute, ColumnAttribute and 
        /// PrimaryKeyAttribute on the record type.
        /// </summary>
        /// <param name="connection">Connection to execute the command on.</param>
        /// <param name="options">
        /// Specifies additional options to be used with the operation.
        /// </param>
        /// <param name="record">
        /// The object that will be used to generate the SQL command and parameters.
        /// </param>
        public static void RecordDelete(this DbConnection connection, IvyOrmOptions options, object record)
        {
            if (connection == null)
                throw new ArgumentNullException("connection");
            if (record == null)
                throw new ArgumentNullException("record");

            using (var command = connection.CreateCommand())
                recordDeleteExecute(command, options, record);
        }
        #endregion

        #region RecordDelete DbTransaction
        /// <summary>
        /// Executes a SQL delete for the provided record. The record must be have one or more 
        /// fields with PrimaryKeyAttribute set. If no records are updated then an exception will
        /// be raised.
        /// 
        /// Additional options can be specified using TableAttribute, ColumnAttribute and 
        /// PrimaryKeyAttribute on the record type.
        /// </summary>
        /// <param name="transaction">Transaction to execute the command on.</param>
        /// <param name="record">
        /// The object that will be used to generate the SQL command and parameters.
        /// </param>
        public static void RecordDelete(this DbTransaction transaction, object record)
        {
            RecordDelete(transaction, new IvyOrmOptions(), record);
        }

        /// <summary>
        /// Executes a SQL delete for the provided record. The record must be have one or more 
        /// fields with PrimaryKeyAttribute set. If no records are updated then an exception will
        /// be raised.
        /// 
        /// Additional options can be specified using TableAttribute, ColumnAttribute and 
        /// PrimaryKeyAttribute on the record type.
        /// </summary>
        /// <param name="transaction">Transaction to execute the command on.</param>
        /// <param name="options">
        /// Specifies additional options to be used with the operation.
        /// </param>
        /// <param name="record">
        /// The object that will be used to generate the SQL command and parameters.
        /// </param>
        public static void RecordDelete(this DbTransaction transaction, IvyOrmOptions options, object record)
        {
            if (transaction == null)
                throw new ArgumentNullException("transaction");
            if (record == null)
                throw new ArgumentNullException("record");
            if (transaction.Connection == null)
                throw new ArgumentException("must have the connection property set", "transaction");

            using (var command = transaction.Connection.CreateCommand())
            {
                command.Transaction = transaction;
                recordDeleteExecute(command, options, record);
            }
        }
        #endregion

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        private static void recordDeleteExecute(DbCommand command, IvyOrmOptions options, object record)
        {
            Debug.Assert(command != null);
            Debug.Assert(command.Connection != null);
            Debug.Assert(record != null);

            options = IvyOrmOptions.Defaults(command.Connection, options);
            Debug.Assert(options != null);

            var factory = options.DbProviderFactory;
            var defaultConverterFactory = options.ValueConverterFactory;
            var modificationOptions = (ModificationOptions)options.ModificationOptions;
            Debug.Assert(defaultConverterFactory != null);
            Debug.Assert(factory != null);

            Func<string, string> formatIdentifier = getFormatIdentifierFunc(modificationOptions);
            Debug.Assert(formatIdentifier != null);


            var classInfo = IvyClassInfo.GetClassInfo(record.GetType());

            if (!classInfo.Columns.Values.Any((c) => c.PrimaryKey))
            {
                var message = "Unable to perform operation on class with no primary key. Class " + record.GetType().FullName + " needs one or more properties with " + typeof(PrimaryKeyAttribute).Name;
                var exception = new NotSupportedException(message);
                Logging.Log(options, TraceEventType.Error, 1879014, message, exception);
                throw exception;
            }
            using (var commandBuilder = factory.CreateCommandBuilder())
            {
                int parameterNo = 0;

                StringBuilder where = generateWhereClause(command, defaultConverterFactory, record, formatIdentifier, classInfo, commandBuilder, ref parameterNo);

                command.CommandText = string.Format(CultureInfo.InvariantCulture, "delete from {0} where {1}", commandBuilder.QuoteIdentifier(formatIdentifier(classInfo.TableName)), where);

                Logging.Log(options, TraceEventType.Information, 1879016, "RecordDelete with the following command: \n{0}" + command.CommandText);

                int updatedRecords = command.ExecuteNonQuery();
                if (updatedRecords == 0 && !((modificationOptions & ModificationOptions.DoNotThrowExceptionOnNoRecordsUpdated) == ModificationOptions.DoNotThrowExceptionOnNoRecordsUpdated))
                {
                    var message = string.Format(CultureInfo.InvariantCulture, "Expected to delete 1 or more record however {0} records were deleted (E0538F8A)", updatedRecords);
                    var exception = new IvyOrmException(message);
                    Logging.Log(options, TraceEventType.Error, 1879015, message, exception);
                    throw exception;
                }
            }
        }

        private static StringBuilder generateWhereClause(DbCommand command, ValueConverterFactory defaultConverterFactory, object record, Func<string, string> getFormatedIdentifier, IvyClassInfo classInfo, DbCommandBuilder commandBuilder, ref int parameterNo)
        {
            //WHERE piece
            StringBuilder where = new StringBuilder();
            foreach (var column in classInfo.Columns.Values.Where((c) => c.PrimaryKey))
            {
                var columnNameQuoted = commandBuilder.QuoteIdentifier(getFormatedIdentifier(column.ColumnName));
                var parameterName = commandBuilder.GetParameterPlaceholder(parameterNo++);
                var converter = (column.ConverterFactory ?? defaultConverterFactory).GetToDatabaseConverter(column.FieldOrPropertyType, column.DatabaseType);
                var parameterValue = converter(column.GetValueUnconverted(record));
                
                if (where.Length != 0)
                    where.Append(" and ");
                
                if (parameterValue == DBNull.Value || parameterValue == null)
                {
                    where.AppendFormat(CultureInfo.InvariantCulture, "{0} is null", columnNameQuoted, parameterName);
                }
                else
                {
                    var parameter = command.CreateParameter();
                    parameter.ParameterName = parameterName;
                    parameter.Value = parameterValue;
                    command.Parameters.Add(parameter);

                    where.AppendFormat(CultureInfo.InvariantCulture, "{0}={1}", columnNameQuoted, parameterName);
                }
            }
            return where;
        }
    }
}
